-- Hospital Management System - Functions & Triggers

SET search_path = hms, public;

---------------------------------------------------
-- ROOM AVAILABILITY UPDATE
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.update_room_availability()
RETURNS TRIGGER AS $$
BEGIN
    NEW.is_available := (NEW.capacity > 0);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_rooms_update_availability ON hms.rooms;
CREATE TRIGGER trg_rooms_update_availability
BEFORE INSERT OR UPDATE ON hms.rooms
FOR EACH ROW
EXECUTE FUNCTION hms.update_room_availability();

---------------------------------------------------
-- PREVENT OVERLAPPING APPOINTMENTS
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.prevent_overlapping_appointments()
RETURNS TRIGGER AS $$
DECLARE
    overlap_count INT;
    start_ts TIMESTAMPTZ := NEW.appointment_datetime;
    end_ts   TIMESTAMPTZ := NEW.appointment_datetime + (NEW.duration_minutes || ' minutes')::INTERVAL;
BEGIN
    SELECT COUNT(*) INTO overlap_count
    FROM hms.appointments
    WHERE staff_id = NEW.staff_id
      AND appointment_id <> COALESCE(NEW.appointment_id, 0)
      AND (
          (appointment_datetime < end_ts)
          AND (appointment_datetime + (duration_minutes || ' minutes')::INTERVAL > start_ts)
      );

    IF overlap_count > 0 THEN
        RAISE EXCEPTION 'Staff already has another appointment during this time.';
    END IF;

    IF NEW.appointment_datetime < now() THEN
        RAISE EXCEPTION 'Appointment must be scheduled in the future.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_appointments_no_overlap ON hms.appointments;
CREATE TRIGGER trg_appointments_no_overlap
BEFORE INSERT OR UPDATE ON hms.appointments
FOR EACH ROW
EXECUTE FUNCTION hms.prevent_overlapping_appointments();

---------------------------------------------------
-- INVOICE STATUS AUTO-UPDATE
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.invoice_status_update()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.paid_amount >= NEW.total_amount THEN
        NEW.status := 'paid';
    ELSIF NEW.paid_amount > 0 AND NEW.paid_amount < NEW.total_amount THEN
        NEW.status := 'partial';
    ELSE
        IF NEW.due_date < current_date THEN
            NEW.status := 'overdue';
        ELSE
            NEW.status := 'unpaid';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_invoices_status ON hms.invoices;
CREATE TRIGGER trg_invoices_status
BEFORE INSERT OR UPDATE ON hms.invoices
FOR EACH ROW
EXECUTE FUNCTION hms.invoice_status_update();

---------------------------------------------------
--  AUDIT LOG TRIGGER
---------------------------------------------------
-- Make sure audit_log table exists
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'hms' AND tablename = 'audit_log') THEN
        RAISE NOTICE 'Table hms.audit_log does not exist. Create it before enabling audit logging.';
    END IF;
END $$;

CREATE OR REPLACE FUNCTION hms.log_audit()
RETURNS TRIGGER AS $$
DECLARE
    entity_name TEXT := TG_TABLE_NAME;
    operation   TEXT := TG_OP;
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO hms.audit_log (entity, entity_id, action, details)
        VALUES (entity_name, NULL, operation, to_jsonb(OLD));
        RETURN OLD;
    ELSE
        INSERT INTO hms.audit_log (entity, entity_id, action, details)
        VALUES (entity_name, NULL, operation, to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_audit_patients ON hms.patients;
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE ON hms.patients
FOR EACH ROW
EXECUTE FUNCTION hms.log_audit();

DROP TRIGGER IF EXISTS trg_audit_appointments ON hms.appointments;
CREATE TRIGGER trg_audit_appointments
AFTER INSERT OR UPDATE OR DELETE ON hms.appointments
FOR EACH ROW
EXECUTE FUNCTION hms.log_audit();

DROP TRIGGER IF EXISTS trg_audit_invoices ON hms.invoices;
CREATE TRIGGER trg_audit_invoices
AFTER INSERT OR UPDATE OR DELETE ON hms.invoices
FOR EACH ROW
EXECUTE FUNCTION hms.log_audit();

---------------------------------------------------
--  CALCULATE TOTAL INVOICE
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.calculate_total_invoice(p_invoice_id INT)
RETURNS NUMERIC(12,2) AS $$
DECLARE
    total NUMERIC(12,2);
BEGIN
    SELECT COALESCE(SUM(amount),0) INTO total
    FROM hms.invoice_items
    WHERE invoice_id = p_invoice_id;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

---------------------------------------------------
--  GET PATIENT BALANCE
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.get_patient_balance(p_patient_id INT)
RETURNS TABLE(invoice_id INT, total NUMERIC, paid NUMERIC, balance NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT invoice_id,
           total_amount,
           paid_amount,
           (total_amount - paid_amount) AS balance
    FROM hms.invoices
    WHERE patient_id = p_patient_id;
END;
$$ LANGUAGE plpgsql;

---------------------------------------------------
-- DECREMENT MEDICATION STOCK
---------------------------------------------------
CREATE OR REPLACE FUNCTION hms.decrement_med_stock()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE hms.medications
    SET stock_qty = stock_qty - 1
    WHERE medication_id = NEW.medication_id;

    IF (SELECT stock_qty FROM hms.medications WHERE medication_id = NEW.medication_id) < 0 THEN
        RAISE EXCEPTION 'Medication stock insufficient for id %', NEW.medication_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_prescription_items_decrement_stock ON hms.prescription_items;
CREATE TRIGGER trg_prescription_items_decrement_stock
AFTER INSERT ON hms.prescription_items
FOR EACH ROW
EXECUTE FUNCTION hms.decrement_med_stock();
